﻿Imports System.Data.OleDb

Public Class AquariumLikingGateway
    Inherits DataGateway

    Protected Overrides ReadOnly Property FindAllQuery As String
        Get
            Return "SELECT * FROM AquariumLiking"
        End Get
    End Property

    Protected Overrides ReadOnly Property DeleteQuery As String
        Get
            Return "DELETE FROM AquariumLiking"
        End Get
    End Property

    Protected Overrides ReadOnly Property SiteUserQuery() As String
        Get
            Return "SELECT SiteUser.* " &
                   "FROM AquariumLiking LEFT JOIN SiteUser " &
                   "ON AquariumLiking.UserID=SiteUser.ID " &
                   "WHERE AquariumLiking.ID=@ID"
        End Get
    End Property

    Public Overloads Sub DeleteByID(ByVal aquariumID As ULong, ByVal userID As ULong)
        Dim sqlQuery As String = DeleteQuery & " WHERE AquariumID=@AquariumID AND UserID=@UserID"
        Using connection As New OleDbConnection(ConnectionString)
            Dim command As New OleDbCommand(sqlQuery, connection)
            command.CommandType = CommandType.Text
            command.Parameters.AddWithValue("@AquariumID", aquariumID)
            command.Parameters.AddWithValue("@UserID", userID)
            connection.Open()
            command.ExecuteNonQuery()
        End Using
    End Sub

    Public Overloads Function FindByID(ByVal aquariumID As ULong, ByVal userID As ULong) As DataTable
        Dim sqlQuery As String = FindAllQuery & "  WHERE UserID=@UserID AND AquariumID=@AquariumID"
        Dim queryParams As New OrderedDictionary()
        queryParams.Add("@UserID", userID)
        queryParams.Add("@AquariumID", aquariumID)
        Return QueryTable(sqlQuery, queryParams)
    End Function

    Public Function UserExpressedLiking(ByVal aquariumID As ULong, ByVal userID As ULong) As Boolean
        Dim rows As DataRowCollection = FindByID(aquariumID, userID).Rows
        Return rows.Count() = 1
    End Function

    Public Sub InsertOrUpdate(ByVal aquariumID As ULong, ByVal userID As ULong, ByVal liking As Boolean)
        If UserExpressedLiking(aquariumID, userID) Then
            Update(aquariumID, userID, liking)
        Else
            Insert(aquariumID, userID, liking)
        End If
    End Sub

    Public Function Insert(ByVal aquariumID As ULong, ByVal userID As ULong, ByVal liking As Boolean) As ULong
        Dim newID As ULong
        Dim sqlQuery As String = "INSERT INTO AquariumLiking (AquariumID, Liking, UserID) VALUES (@AquariumID, @Liking, @UserID)"
        Using connection As New OleDbConnection(ConnectionString)
            Using command As New OleDbCommand(sqlQuery, connection)
                command.CommandType = CommandType.Text
                command.Parameters.AddWithValue("@AquariumID", aquariumID)
                command.Parameters.AddWithValue("@Liking", liking)
                command.Parameters.AddWithValue("@UserID", userID)
                connection.Open()
                command.ExecuteNonQuery()
                newID = GetLastInsertID(command)
            End Using
        End Using

        Return newID
    End Function

    Public Sub Update(ByVal aquariumID As ULong, ByVal userID As ULong, ByVal liking As Boolean)
        Dim sqlQuery As String = "UPDATE AquariumLiking SET Liking=@Liking WHERE AquariumID=@AquariumID AND UserID=@UserID"
        Using connection As New OleDbConnection(ConnectionString)
            Using command As New OleDbCommand(sqlQuery, connection)
                command.CommandType = CommandType.Text
                command.Parameters.AddWithValue("@Liking", liking)
                command.Parameters.AddWithValue("@AquariumID", aquariumID)
                command.Parameters.AddWithValue("@UserID", userID)
                connection.Open()
                Dim rowsAffected As Integer = command.ExecuteNonQuery()
            End Using
        End Using
    End Sub
End Class
